﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public partial class Users
    {
        DbHelperSQL dbhelpersql = new DbHelperSQL();

        #region 查询全部管理员，返回实体对象集合
        /// <summary>
        /// 查询全部管理员，返回实体对象集合
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<MODEL.Users> getAdminModel()
        {
            string sql = "select * from Users u,UserRoles ur,UserStates us where u.UserRoleId=ur.UrId and UserRoleId=2 and u. UserStateId =us.UsId and u. UserStateId=1;";
            DataTable dt = dbhelpersql.GetDataTable(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                List<MODEL.Users> list = new List<MODEL.Users>();
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Users model = new MODEL.Users();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        }
        #endregion

        #region 根据用户名密码判断管理员是否存在，存在则允许登录
        /// <summary>
        /// 根据用户名密码判断管理员是否存在，存在则允许登录
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool ExistAdmin(string name, string pwd)
        {
            string sql = "select * from Users where UName=@UName and Pwd=@pwd and UserStateId=1 and (UserRoleId=2 or UserRoleId=3);";
            SqlParameter[] paras = {
                                       new SqlParameter("@UName",name),
                                       new SqlParameter("@pwd",pwd)
                                   };
            int r = dbhelpersql.GetScalar(sql, paras);
            return r > 0 ? true : false;
        }
        #endregion

        #region 添加管理员
        /// <summary>
        /// 添加管理员
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddAdmin(MODEL.Users model)
        {
            StringBuilder sb = new StringBuilder("insert into Users(UName,Pwd,Question,Answer,Email,QQ,Phone,UserRoleId)values(@UName,@Pwd,@Question,@Answer,@Email,@QQ,@Phone,@UserRoleId)");
            SqlParameter[] paras = {new SqlParameter("@UName",model.UName),
                                    new SqlParameter("@Pwd",model.Pwd),
                                    new SqlParameter("@Question",model.Question),
                                    new SqlParameter("@Answer",model.Answer),
                                    new SqlParameter("@Email",model.Email),
                                    new SqlParameter("@QQ",model.QQ),
                                    new SqlParameter("@Phone",model.Phone),
                                    new SqlParameter("@UserRoleId",model.UserRoleId)
                                   };
            if (Exist(model.UName))
            {
                return false;
            };
            int r = dbhelpersql.ExecuteCommand(sb.ToString(), paras);
            if (r > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 查询全部用户，返回实体对象集合
        /// <summary>
        /// 查询全部用户，返回实体对象集合
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public List<MODEL.Users> getModel()
        {
            string sql = "select * from Users u,UserRoles ur,UserStates us where u.UserRoleId=ur.UrId and UserRoleId=1 and u. UserStateId =us.UsId and u. UserStateId=1;";
            DataTable dt = dbhelpersql.GetDataTable(sql);
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                List<MODEL.Users> list = new List<MODEL.Users>();
                foreach (DataRow dr in dt.Rows)
                {
                    MODEL.Users model = new MODEL.Users();
                    setDr2Model(dr, model);
                    list.Add(model);
                }
                return list;
            }
        }
        #endregion

        #region 根据用户名重置密码
        /// <summary>
        /// 根据用户名重置密码
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool resetPwdByName(string name, string pwd)
        {
            string sql = "update Users set Pwd=@Pwd where UName=@UName;";
            SqlParameter[] paras = { 
                                      new SqlParameter("@UName",name),
                                      new SqlParameter("@Pwd",pwd)
                                   };
            int r = dbhelpersql.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 根据用户名,密保问题与答案判断是否存在该用户
        /// <summary>
        /// 根据密保问题与答案判断是否存在该用户
        /// </summary>
        /// <param name="question"></param>
        /// <param name="answer"></param>
        /// <returns></returns>
        public bool isExistOrNotByQA(string name,string question, string answer)
        {
            string sql = "select * from Users where UName=@UName and Question=@Question and Answer=@Answer ;";
            SqlParameter[] paras = { 
                                      new SqlParameter("@UName",name),
                                      new SqlParameter("@Question",question),
                                      new SqlParameter("Answer",answer)
                                   };
            int r = dbhelpersql.GetScalar(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 根据用户名密码判断用户是否存在，存在则允许登录
        /// <summary>
        /// 根据用户名密码判断用户是否存在，存在则允许登录
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool Exist(string name, string pwd)
        {
            string sql = "select * from Users where UName=@UName and Pwd=@pwd and UserStateId=1;";
            SqlParameter[] paras = {
                                       new SqlParameter("@UName",name),
                                       new SqlParameter("@pwd",pwd)
                                   };
            int r = dbhelpersql.GetScalar(sql, paras);
            return r > 0 ? true : false;
        } 
        #endregion

        #region 根据用户名查询数据，返回实体对象
        /// <summary>
        /// 根据用户名查询数据，返回实体对象
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public MODEL.Users getModel(string name)
        {
            string sql = "select * from Users where UName=@UName and UserStateId=1;";
            SqlParameter[] paras = { new SqlParameter("@UName", name) };
            DataRow dr = dbhelpersql.GetDataRow(sql, paras);
            if (dr == null)
            {
                return null;
            }
            else
            {
                MODEL.Users model = new MODEL.Users();
                setDr2Model(dr, model);
                return model;
            }
        } 
        #endregion

        #region 根据Id查询数据，返回实体对象
        /// <summary>
        /// 根据用户名查询数据，返回实体对象
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public MODEL.Users getModel(int UId)
        {
            string sql = "select * from Users where UId=@UId and UserStateId=1;";
            SqlParameter[] paras = { new SqlParameter("@UId", UId) };
            DataRow dr = dbhelpersql.GetDataRow(sql, paras);
            if (dr == null)
            {
                return null;
            }
            else
            {
                MODEL.Users model = new MODEL.Users();
                setDr2Model(dr, model);
                return model;
            }
        }
        #endregion

        #region 对用户资料进行更改(后台）
        /// <summary>
        /// 对用户资料进行更改(后台）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(MODEL.Users model)
        {
            string sql = "update Users set UName=@UName,Pwd=@Pwd,Question=@Question,Answer=@Answer,Email=@Email,QQ=@QQ,Phone=@Phone,UserRoleId=@UserRoleId,UserStateId=@UserStateId where UId=@UId;";
            SqlParameter[] paras = {
                                       new SqlParameter("@UName",model.UName),
                                       new SqlParameter("@Pwd",model.Pwd),
                                       new SqlParameter("@Question",model.Question),
                                       new SqlParameter("@Answer",model.Answer),
                                       new SqlParameter("@Email",model.Email),
                                       new SqlParameter("@QQ",model.QQ),
                                       new SqlParameter("@Phone",model.Phone),
                                       new SqlParameter("@UId",model.UId),
                                       new SqlParameter("@UserRoleId",model.UserRoleId),
                                       new SqlParameter("@UserStateId",model.UserStateId)
                                   };
            if (Exist(model))
            {
                return false;
            }
            int r = dbhelpersql.ExecuteCommand(sql, paras);
            if (r > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 根据Id对用户资料进行更改
        /// <summary>
        /// 用户资料更改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool modify(MODEL.Users model)
        {
            string sql = "update Users set UName=@UName,Pwd=@Pwd,Question=@Question,Answer=@Answer,Email=@Email,QQ=@QQ,Phone=@Phone where UId=@UId;";
            SqlParameter[] paras = {
                                       new SqlParameter("@UName",model.UName),
                                       new SqlParameter("@Pwd",model.Pwd),
                                       new SqlParameter("@Question",model.Question),
                                       new SqlParameter("@Answer",model.Answer),
                                       new SqlParameter("@Email",model.Email),
                                       new SqlParameter("@QQ",model.QQ),
                                       new SqlParameter("@Phone",model.Phone),
                                       new SqlParameter("@UId",model.UId)
                                   };
            if (Exist(model))
            {
                return false;
            }
            int r = dbhelpersql.ExecuteCommand(sql, paras);
            return r > 0;
        } 
        #endregion

        #region 新增用户，用户注册
        /// <summary>
        /// 新增用户，用户注册
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Add(MODEL.Users model)
        {
            StringBuilder sb = new StringBuilder("insert into Users(UName,Pwd,Question,Answer,Email,QQ,Phone)values(@UName,@Pwd,@Question,@Answer,@Email,@QQ,@Phone)");
            SqlParameter[] paras = {new SqlParameter("@UName",model.UName),
                                    new SqlParameter("@Pwd",model.Pwd),
                                    new SqlParameter("@Question",model.Question),
                                    new SqlParameter("@Answer",model.Answer),
                                    new SqlParameter("@Email",model.Email),
                                    new SqlParameter("@QQ",model.QQ),
                                    new SqlParameter("@Phone",model.Phone)
                                   };
            if (Exist(model.UName))
            {
                return false;
            };
            int r = dbhelpersql.ExecuteCommand(sb.ToString(), paras);
            if (r > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        } 
        #endregion

        #region 根据用户名判断用户是否存在
        /// <summary>
        /// 根据用户名判断用户是否存在
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool Exist(string userName)
        {
            string sql = "select * from Users where [UName]=@UName and UserStateId=1;";
            SqlParameter[] paras = { new SqlParameter("@UName", userName) };
            int r = dbhelpersql.GetScalar(sql, paras);
            if (r > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        } 
        #endregion

        #region 根据用户名以及UId判断用户是否存在
        /// <summary>
        /// 根据用户名以及UId判断用户是否存在
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool Exist(MODEL.Users model)
        {
            string sql = "select * from Users where [UName]=@UName and UserStateId=1 and UId!=@UId;";
            SqlParameter[] paras = { new SqlParameter("@UName", model.UName), 
                                     new SqlParameter("@UId",model.UId)
                                   };
            int r = dbhelpersql.GetScalar(sql, paras);
            if (r > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 将数据行转换成实体对象
        /// <summary>
        /// 将数据行转换成实体对象
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="model"></param>
        public void setDr2Model(DataRow dr, MODEL.Users model)
        {
            if (dr.Table.Columns.Contains("UId") && !string.IsNullOrEmpty(dr["UId"].ToString()))
            {
                model.UId = Convert.ToInt32(dr["UId"]);
            }
            if (dr.Table.Columns.Contains("UName") && !string.IsNullOrEmpty(dr["UName"].ToString()))
            {
                model.UName = dr["UName"].ToString();
            }
            if (dr.Table.Columns.Contains("Pwd") && !string.IsNullOrEmpty(dr["Pwd"].ToString()))
            {
                model.Pwd = dr["Pwd"].ToString();
            }
            if (dr.Table.Columns.Contains("Question") && !string.IsNullOrEmpty(dr["Question"].ToString()))
            {
                model.Question = dr["Question"].ToString();
            }
            if (dr.Table.Columns.Contains("Answer") && !string.IsNullOrEmpty(dr["Answer"].ToString()))
            {
                model.Answer = dr["Answer"].ToString();
            }
            if (dr.Table.Columns.Contains("Email") && !string.IsNullOrEmpty(dr["Email"].ToString()))
            {
                model.Email = dr["Email"].ToString();
            }
            if (dr.Table.Columns.Contains("QQ") && !string.IsNullOrEmpty(dr["QQ"].ToString()))
            {
                model.QQ = dr["QQ"].ToString();
            }
            if (dr.Table.Columns.Contains("Phone") && !string.IsNullOrEmpty(dr["Phone"].ToString()))
            {
                model.Phone = dr["Phone"].ToString();
            }
            if (dr.Table.Columns.Contains("UserRoleId") && !string.IsNullOrEmpty(dr["UserRoleId"].ToString()))
            {
                //model.UserRole= dr["UserRoleId"].ToString();
                model.UserRoleId = Convert.ToInt32(dr["UserRoleId"]);
                new DAL.UserRoles().setDr2Model(dr, model.UserRole);
            }
            if (dr.Table.Columns.Contains("UserStateId") && !string.IsNullOrEmpty(dr["UserStateId"].ToString()))
            {
                //model.UserRole= dr["UserRoleId"].ToString();
                model.UserStateId = Convert.ToInt32(dr["UserStateId"]);
                new DAL.UserStates().setDr2Model(dr, model.UserState);
            }
        } 
        #endregion
    }
}
